﻿using System.Data;
using System.Data.SqlClient;
using System.Threading;

namespace com.fukefu.sql
{
    public class SqlServerOperate
    {
        //单一模式
        public static object obj = new object();
        public static SqlServerOperate sql;
        public static SqlServerOperate GetInstance()
        {
            if (sql == null)
            {
                lock (obj)
                {
                    if (sql == null)
                    {
                        sql = new SqlServerOperate();
                    }
                }
            }
            return sql;
        }

        string url, user, pass, database;
        int timeout = 360 * 1000;

        SqlConnection conn, conn_update;
        SqlDataReader re;

        public void SetTimeout(int second)
        {
            timeout = second * 1000;
        }
        //构造函数
        public SqlServerOperate()
        {
            this.url = Config.Sql_url;
            this.user = Config.Sql_name;
            this.pass = Config.Sql_pass;
            this.database = Config.Sql_database;
            //查询连接
            conn = new SqlConnection("server=" + url + ";database=" + database + ";uid=" + user + ";pwd=" + pass);
            conn.Open();
            //更新连接
            conn_update = new SqlConnection("server=" + url + ";database=" + database + ";uid=" + user + ";pwd=" + pass); ;
            conn_update.Open();
        }
        //连接字符串
        public string GetConnectionString()
        {
            return @"server=" + url + ";uid=" + user + ";pwd=" + pass + ";database=" + database;
        }
        //构造函数
        public SqlServerOperate(string url, string name, string pass, string database)
        {
            this.url = url;
            this.user = name;
            this.pass = pass;
            this.database = database;
            //查询连接
            conn = new SqlConnection("server=" + url + ";database=" + database + ";uid=" + user + ";pwd=" + pass);
            conn.Open();
            //更新连接
            conn_update = new SqlConnection("server=" + url + ";database=" + database + ";uid=" + user + ";pwd=" + pass); ;
            conn_update.Open();
        }
        //获取查询数量 
        public int GetQueryNum(string cmd)
        {
            int size = 0;
            re = Query(cmd);
            if (re.Read())
            {
                size = int.Parse(re[0].ToString());
            }
            re.Close();
            return size;
        }
        public int GetMaxId(string cmd)
        {
            int size = 0;
            re = Query(cmd);
            if (re.Read())
            {
                size = int.Parse(re[0].ToString());
            }
            re.Close();
            return size;
        }
        //查询
        public SqlDataReader Query(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandTimeout = timeout;
            re = cmd.ExecuteReader();
            return re;
        }
        //截断表
        public void Truncate(string name)
        {
            Update("truncate table " + name);
        }
        //防止大批量查询机器反应不过来，更新的时候锁死表
        public int Update(string sql)
        {
            lock (conn_update)
            {
                SqlCommand cmd = new SqlCommand(sql, conn_update);
                cmd.CommandTimeout = timeout;
                if (conn_update.State == ConnectionState.Broken || conn_update == null || conn_update.State == ConnectionState.Closed)
                {
                    //更新连接
                    conn_update = new SqlConnection("server=" + url + ";database=" + database + ";uid=" + user + ";pwd=" + pass); ;
                    conn_update.Open();
                }
                Thread.Sleep(10);
                return cmd.ExecuteNonQuery();
            }
        }
        //获取dateset
        public DataSet GetDataSet(string cmd)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter re = new SqlDataAdapter(cmd, conn);
            re.Fill(ds, "table1");
            return ds;
        }
        //关闭
        public void Close()
        {
            conn.Close();
            conn_update.Close();
        }
    }
}